<?php


namespace App\Services;


use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Illuminate\Http\Request;

class WarrantyService
{
    public function importData(Request $request)
    {
        set_time_limit(0);
        $file = $request->file('upload_excel');
        if (empty($file)) {
            echo "缺少文件";
            return false;
        }

        $date = date('Y-m-d H:i:s');
        $region_master = DB::table('region_master')
            ->get();
        $province_master = DB::table('province_master')->get();
        $city_master = DB::table('cities')->get();
        $region_en = $region_zh_tw = $region_zh = [];
        $province_en = $province_zh_tw = $province_zh = [];
        $city_en = $city_zh_tw = $city_zh = [];
        foreach ($region_master as $k => $v) {
            $region_en[$v->english_value] = $v->id;
            $region_zh_tw[$v->traditional_chiness_value] = $v->id;
            $region_zh[$v->simplified_chiness_value] = $v->id;
        }
        foreach ($province_master as $k => $v) {
            $province_en[$v->english_value] = $v->id;
            $province_zh_tw[$v->traditional_chiness_value] = $v->id;
            $province_zh[$v->simplified_chiness_value] = $v->id;
        }
        foreach ($city_master as $k => $v) {
            $city_en[$v->english_value] = $v->id;
            $city_zh_tw[$v->traditional_chiness_value] = $v->id;
            $city_zh[$v->simplified_chiness_value] = $v->id;
        }
        // 装贴门店表
        $user_master = DB::table('users')->get(['id', 'abbr', 'status', 'creator_id']);
        $users = [];
        $user_kv = [];
        foreach ($user_master as $k => $v) {
            $user_kv[$v->id] = [
                'abbr' => $v->abbr,
                'creator_id' => $v->creator_id,
            ];
            if ($v->status == 1) {
                if (strpos($v->id, '-') === false) {
                    $key = trim($v->abbr);
                    $users[$key] = $v->id;
                }
            }
        }
        foreach ($user_master as $k => $v) {
            if ($v->status == 1) {
                $key = trim($v->abbr);
                if (empty($users[$key])) {
                    $users[$key] = $v->id;
                }
            }
        }
        foreach ($user_master as $k => $v) {
            $key = trim($v->abbr);
            if (empty($users[$key])) {
                $users[$key] = $v->id;
            }
        }
        // 安装部位
        $part = DB::table('part_master')
            ->get();
        $part_en = $part_zh_tw = $part_zh = [];
        foreach ($part as $k => $v) {
            $part_en[$v->english_value] = $v->id;
            $part_zh_tw[$v->traditional_chiness_value] = $v->id;
            $part_zh[$v->simplified_chiness_value] = $v->id;
        }
        // 膜的种类
        $film = DB::table('film_type_master')
            ->get();
        $film_en = $film_zh_tw = $film_zh = [];
        foreach ($film as $k => $v) {
            $film_en[$v->english_value] = $v->id;
            $film_zh_tw[$v->traditional_chiness_value] = $v->id;
            $film_zh[$v->simplified_chiness_value] = $v->id;
        }
        // 品牌
        $make = DB::table('make_master')->get();
        $make_en = $make_zh_tw = $make_zh = [];
        foreach ($make as $k => $v) {
            $make_en[$v->year_id . '#' . $v->english_value] = $v->id;
            $make_zh_tw[$v->year_id . '#' . $v->traditional_chiness_value] = $v->id;
            $make_zh[$v->year_id . '#' . $v->simplified_chiness_value] = $v->id;
        } 
        //型号
        $models = getModelList(); 

        $filePath = $file->getRealPath();
        if (!file_exists($filePath) || !is_file($filePath)) {
            return [];
        }

        $spreadsheet = IOFactory::load($filePath);
        $raw = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        $arrays = [];
        foreach ($raw as $k => $v) {
            if ($v['A'] == '姓名') {
                continue;
            }
            if (empty($v['A'])) {
                continue;
            }
            $arrays[] = $v;
        }
        $region = Arr::pluck($arrays, 'B');
        $province = Arr::pluck($arrays, 'C');
        $city = Arr::pluck($arrays, 'D');
        $region = array_unique($region);
        $province = array_unique($province);
        $city = array_unique($city);

        $regionIs = [];
        foreach ($region as $k => $v) {
            if (empty($v)) {
                $v = '中国大陆';
            } else {
                if ($v == '中国') {
                    $v = '中国大陆';
                }
                if ($v == '台湾') {
                    $v = '台湾地区';
                }
                if ($v == '澳门') {
                    $v = '澳门特别行政区';
                }
                if ($v == '香港') {
                    $v = '香港特别行政区';
                }
            }
            $region[$k] = $v;
        }

        $errors = [];
        $regions = [];
        $region_error = [];
        foreach ($region as $k => $v) {
            if (!empty($region_en[$v])) {
                $regions[$v] = $region_en[$v];
            } elseif (!empty($region_zh_tw[$v])) {
                $regions[$v] = $region_zh_tw[$v];
            } elseif (!empty($region_zh[$v])) {
                $regions[$v] = $region_zh[$v];
            } else {
                $region_error[] = $v;
            }
        }
        if (!empty($region_error)) {
            $errors[] = '以下地区不存在：' . implode('，', $region_error);
        }
        foreach ($province as $k => $v) {
            if (mb_strpos($v, '宁夏') === 0) {
                $v = '宁夏';
            }
            if (mb_strpos($v, '内蒙古') === 0) {
                $v = '内蒙古';
            }
            if (mb_strpos($v, '西藏') === 0) {
                $v = '西藏';
            }
            if (mb_strpos($v, '新疆') === 0) {
                $v = '新疆';
            }
            if (mb_strpos($v, '香港') === 0) {
                $v = '香港';
            }
            if (mb_strpos($v, '澳门') === 0) {
                $v = '澳门';
            }
            if (mb_strpos($v, '台湾') === 0) {
                $v = '台湾';
            }
            $province[$k] = $v;
        }
        $provinces = [];
        $province_error = [];
        foreach ($province as $k => $v) {
            $v1 = $v;
            if (mb_strpos($v, '宁夏') === 0) {
                $v1 = '宁夏';
            }
            if (mb_strpos($v, '内蒙古') === 0) {
                $v1 = '内蒙古';
            }
            if (mb_strpos($v, '西藏') === 0) {
                $v1 = '西藏';
            }
            if (mb_strpos($v, '新疆') === 0) {
                $v1 = '新疆';
            }
            if (mb_strpos($v, '香港') === 0) {
                $v1 = '香港';
            }
            if (mb_strpos($v, '澳门') === 0) {
                $v1 = '澳门';
            }
            if (mb_strpos($v, '台湾') === 0) {
                $v1 = '台湾';
            }
            $v2 = $v . '省';
            $v3 = $v . '市';
            if (!empty($province_en[$v])) {
                $provinces[$v] = $province_en[$v];
            } elseif (!empty($province_zh_tw[$v])) {
                $provinces[$v] = $province_zh_tw[$v];
            } elseif (!empty($province_zh[$v])) {
                $provinces[$v] = $province_zh[$v];
            } elseif (!empty($province_en[$v1])) {
                $provinces[$v] = $province_en[$v1];
            } elseif (!empty($province_zh_tw[$v1])) {
                $provinces[$v] = $province_zh_tw[$v1];
            } elseif (!empty($province_zh[$v1])) {
                $provinces[$v] = $province_zh[$v1];
            } elseif (!empty($province_en[$v2])) {
                $provinces[$v] = $province_en[$v2];
            } elseif (!empty($province_zh_tw[$v2])) {
                $provinces[$v] = $province_zh_tw[$v2];
            } elseif (!empty($province_zh[$v2])) {
                $provinces[$v] = $province_zh[$v2];
            } elseif (!empty($province_en[$v3])) {
                $provinces[$v] = $province_en[$v3];
            } elseif (!empty($province_zh_tw[$v3])) {
                $provinces[$v] = $province_zh_tw[$v3];
            } elseif (!empty($province_zh[$v3])) {
                $provinces[$v] = $province_zh[$v3];
            } else {
                $province_error[] = $v;
            }
        }
        if (!empty($province_error)) {
            $errors[] = '以下省不存在：' . implode('，', $province_error);
        }
        $citys = [];
        $city_error = [];
        foreach ($city as $k => $v) {
            $v3 = $v . '市';
            if (!empty($city_en[$v])) {
                $citys[$v] = $city_en[$v];
            } elseif (!empty($city_zh_tw[$v])) {
                $citys[$v] = $city_zh_tw[$v];
            } elseif (!empty($city_zh[$v])) {
                $citys[$v] = $city_zh[$v];
            } elseif (!empty($city_en[$v3])) {
                $citys[$v] = $city_en[$v3];
            } elseif (!empty($city_zh_tw[$v3])) {
                $citys[$v] = $city_zh_tw[$v3];
            } elseif (!empty($city_zh[$v3])) {
                $citys[$v] = $city_zh[$v3];
            } else {
                $city_error[] = $v;
            }
        }
        if (!empty($city_error)) {
            $errors[] = '以下城市不存在：' . implode('，', $city_error);
        }



        $errors = [];
        $uploads = [];
        $wvcs = [];
        $name_error = [];
        $region_error = [];
        $city_error = [];
        $province_error = [];
        $phone_error = [];
        $address_error = [];

        $make_error = [];
        $model_error = [];
        $user_error = [];
        $time_error = [];
        $part_error = [];
        $film_error = [];
        $time = Arr::pluck($arrays, 'P');
        $ts = [];
        $es = [];
        foreach ($time as $v) {
            $C11 = trim($v);
            $installation_date = strtotime($C11);
            $es[] = $installation_date;
            $installation_date = date('Y-m-d', $installation_date);
            $ts[] = $installation_date;
        }
        foreach ($arrays as $k => $v) {
            $mkey = $v['I'] . '#' . $v['J']; 
            $make_id = 0;
            $model_id = 0;
            $user_id = 0;
            $dealer_name = '';
            $distributor_id = 0;
            $distributor_name = ''; 

            if (!empty($make_en[$mkey])) {
                $make_id = $make_en[$mkey];
            } elseif (!empty($make_zh_tw[$mkey])) {
                $make_id = $make_zh_tw[$mkey];
            } elseif (!empty($make_zh[$mkey])) {
                $make_id = $make_zh[$mkey];
            } else {
                $make_error[] = $mkey;
            }

            $model_id = (int) @$models[$v['K']];  
            $un = trim($v['N']);
            if (empty($users[$un])) {
                $user_error[] = $un;
            } else {
                $user_id = $users[$un];
                $dealer_name = $un;
                if (!empty($user_kv[$user_id])) {
                    $distributor_id = $user_kv[$user_id]['creator_id'];
                    $distributor_name = $users[$distributor_id] ?? '';
                }
            }
            $C11 = $v['P'];
            $installation_date = '';
            try {
                if (is_string($C11)) {
                    $C11 = trim($C11);
                    if (mb_strpos($C11, '年')) {
                        $idate_arr = date_parse_from_format('Y年m月d日', $C11);
                        $installation_date = mktime(0, 0, 0, $idate_arr['month'], $idate_arr['day'], $idate_arr['year']);
                        $installation_date = date('Y-m-d', $installation_date);
                    } else {
                        $C11 = str_replace('.', '/', $C11);
                        $installation_date = strtotime($C11);
                        $installation_date = date('Y-m-d', $installation_date);
                    }
                } else {
                    $installation_date = Date::excelToTimestamp($C11);
                    $installation_date = date('Y-m-d', $installation_date);
                }
            } catch (\Exception $exception) {
                $time_error[] = $v['P'];
            }

            $precut_or_manual = $v['S'] == '手工' ? 2 : 1;
            $roll_number = $v['T'];
            $length = (int) $v['U'];
            $width = (int) $v['W'];
            $additional_comments = $v['X'];
            $part_id = $film_type_id = 0;

            # part_id
            $par = trim($v['R']);
            if (!empty($part_en[$par])) {
                $part_id = $part_en[$par];
            } elseif (!empty($part_zh_tw[$par])) {
                $part_id = $part_zh_tw[$par];
            } elseif (!empty($part_zh[$par])) {
                $part_id = $part_zh[$par];
            } else {
                $part_error[] = $par;
            }

            # film_type_id
            $fil = trim($v['V']);
            if (!empty($film_en[$fil])) {
                $film_type_id = $film_en[$fil];
            } elseif (!empty($film_zh_tw[$fil])) {
                $film_type_id = $film_zh_tw[$fil];
            } elseif (!empty($film_zh[$fil])) {
                $film_type_id = $film_zh[$fil];
            } else {
                $film_error[] = $fil;
            }


            $rng = trim($v['B']);
            if (empty($rng)) {
                $rng = '中国大陆';
            }
            $region_id = 0;
            $province_id = 0;
            $city = 0;
            if (empty($regions[$rng])) {
                $region_error[] = $v['B'];
            } else {
                $region_id = $regions[$rng];
            }
            $pnm = trim($v['C']);
            if (empty($provinces[$pnm])) {
                $province_error[] = $v['C'];
            } else {
                $province_id = $provinces[$pnm];
            }
            $cnm = trim($v['D']);
            if (empty($citys[$cnm])) {
                $city_error[] = $v['D'];
            } else {
                $city = $citys[$cnm];
            }
            $year = (int) trim($v['I']);

            $item = [
                'warranty_id' => date('ymd') . mt_rand(11111, 99999),
                'first_name' => $v['A'],
                'region_id' => $region_id,
                'province_id' => $province_id,
                'city' => $city,
                'zip' => (int) trim($v['E']),
                'email_address' => trim($v['F']),
                'phone_number' => trim($v['G']),
                'address' => trim($v['H']),
                'year_id' => $year,
                'make_id' => $make_id,
                'model_id' => $model_id,
                'license_plate' => trim($v['L']),
                'vin_number' => trim($v['M']),
                'user_id' => $user_id, // 经销商ID
                'dealer_name' => $dealer_name, // 经销商名称
                'distributor_id' => $distributor_id, // 省代ID
                'distributor_name' => $distributor_name, // 省代名称
                'installation_price' => trim($v['Q']),
                'installation_date' => $installation_date,
                'installer_name' => trim($v['O']),
                'additional_comments' => trim($v['X']),
                'last_name' => '',
                'image_front' => '',
                'image_back' => '',
                'image_driver_side' => '',
                'image_passenger_side' => '',
                'sub_model_id' => 0,
                'vehicle_coverage_id' => 0,
                'pre_id' => 0,
                'language_id' => 0,
                'extension' => '+86',
                'approved' => 1,
                'approved_by_user_id' => 1,
                'approved_date' => $date,
                'created_at' => $date,
                'updated_at' => $date,
            ];
            $coverage = [
                'part_id' => $part_id,
                'precut_or_manual' => $precut_or_manual,
                'roll_number' => $roll_number,
                'film_type_id' => $film_type_id,
                'width' => $width,
                'length' => $length,
                'created_at' => $date,
                'updated_at' => $date,
            ];
            $uploads[] = [
                'warranty' => $item,
                'coverage' => $coverage,
            ];
        }
        if (!empty($region_error)) {
            $errors[] = '以下地区不存在：' . implode('，', $region_error);
        }
        if (!empty($province_error)) {
            $errors[] = '以下省不存在：' . implode('，', $province_error);
        }
        if (!empty($city_error)) {
            $errors[] = '以下城市不存在：' . implode('，', $city_error);
        }
        if (!empty($make_error)) {
            $errors[] = '以下年份内车辆品牌不存在：' . implode('，', $make_error);
        }
        if (!empty($model_error)) {
            $errors[] = '以下年份内车辆型号不存在：' . implode('，', $model_error);
        }
        
        if (!empty($user_error)) {
            $errors[] = '以下转贴门店不存在：' . implode('，', $user_error);
        }
        if (!empty($time_error)) {
            $errors[] = '以下安装日期无法正常转换：' . implode('，', $time_error);
        }
        if (!empty($part_error)) {
            $errors[] = '以下安装部位不存在：' . implode('，', $part_error);
        }
        if (!empty($film_error)) {
            $errors[] = '以下膜的种类不存在：' . implode('，', $film_error);
        }
        if (empty($errors)) {
            $state = true;
            DB::beginTransaction();
            foreach ($uploads as $k => $v) {
                if (!$state) {
                    continue;
                }
                $warranty = DB::table('warranty')->where('vin_number', $v['warranty']['vin_number'])
                    ->where('license_plate', $v['warranty']['license_plate'])
                    ->first();
                if (!empty($warranty)) {
                    $updateData = array_filter($v['warranty']);
                    $updateData['deleted_at'] = null;
                    unset($updateData['warranty_id']);
                    unset($updateData['created_at']);
                    $updateResult = DB::table('warranty')->where('id', $warranty->id)->update($updateData);
                    if (!$updateResult) {
                        DB::rollBack();
                        $state = false;
                        $errors[] = '数据库更新失败，自动回滚；';
                    }
                    $id = $warranty->id;
                } else {
                    $id = DB::table('warranty')->insertGetId($v['warranty']);
                    if (empty($id)) {
                        DB::rollBack();
                        $state = false;
                        $errors[] = '数据库插入失败，自动回滚；';
                    }
                    $v['warranty']['id'] = $id;
                }

                $wvc = $v['coverage'];
                $wvc['warranty_id'] = $id;
                DB::table('warranty_to_vehicle_coverage')->where(['warranty_id' => $id])->delete();

                $wid = DB::table('warranty_to_vehicle_coverage')
                    ->insertGetId($wvc);
                if (empty($wid)) {
                    DB::rollBack();
                    $state = false;
                    $errors[] = '数据库插入失败，自动回滚；';
                }
                $uploads[$k] = [
                    'warranty' => $v['warranty'],
                    'coverage' => $wvc,
                ];
            }
            if ($state) {
                DB::commit();
                exit('全部导入成功');
            } else {
                exit(implode(',', $errors));
            }

            exit(implode(',', $uploads));
        }

        exit(implode(',', $errors));
    }
}
